package dao;

import utils.DBHelper;
import utils.MonthUtil;

import java.sql.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ItemDao {
    // 订单详情
    public Map<String, Object> query(String product, String type,String begin, String end, int page, int size){
        String sql = "select p.product, p.type, i.count, p.price from plist p ,(select i.pid, count(i.pid) count, o.date from orderitem i, `order` o where o.id=i.oid group by i.pid) i where p.id = i.pid ";
        List<Object> params = new ArrayList<>();
        if (product != null && product.trim().isEmpty() == false){
            sql += " and p.product = ?";
            params.add(product);
        }
        if (type != null && type.trim().isEmpty() == false){
            sql += " and p.type = ?";
            params.add(type);
        }
        if (begin != null && begin.trim().isEmpty() == false){
            sql += " and date >= ? and date <= ?";
            params.add(Date.valueOf(begin));
            params.add(Date.valueOf(end));
        }
        long total = DBHelper.count(sql, params.toArray());
        sql += " limit ?, ?";
        params.add(size * (page - 1));
        params.add(size);
        List<Map<String, Object>> data = DBHelper.selectListMap(sql, params.toArray());
        Map<String, Object> res = new HashMap<>();
        res.put("total", total);
        res.put("data", data);
        return res;
    }

    // 类型销量曲线图查询
    public List<Map<String, Object>> queryQXT(String month){
        month = MonthUtil.tran(month);
        String sql = "select p.type, sum(i.count) sum, i.date from plist p,\n" +
                " (select i.pid, sum(i.count) count, SUBSTR(o.date FROM 6 FOR 2) date from `order` o, orderitem i where i.oid = o.id and o.date like  CONCAT('%',SUBSTR(CURDATE(),1, 4), '%' ) group by i.pid) i\n" +
                " where i.pid = id and date = ? GROUP BY p.type";
        return DBHelper.selectListMap(sql, month);
    }

    // 销售额柱状图查询
    public List<Map<String, Object>> queryZZT(String month){
        month = MonthUtil.tran(month);
        String sql = "select SUBSTR(DATE FROM 6 FOR 2) month, SUM(amount) sum from `order` where SUBSTR(date FROM 6 FOR 2) = ?";
        return DBHelper.selectListMap(sql, month);
    }

    // 当天销售额查询
    public String queryAmount(){
        String sql = "select sum(amount) sum from `order` where SUBSTR(date FROM 9 FOR 2) = SUBSTR(CURDATE() FROM 9 FOR 2)";
        return String.valueOf(DBHelper.selectListMap(sql).get(0).get("sum")) ;
    }
}
